package vip.xiaonuo.smzq.core.tools.connection;

/**
 * @author eomer
 * @title SqliteUtils
 * @date 2023/03/04 17:33
 * @description sqlite jdbc 工具类
 */


import org.apache.commons.lang3.StringUtils;
import vip.xiaonuo.smzq.core.tools.connection.entityVo.FieldInfo;

import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.sql.Date;
import java.sql.*;
import java.util.*;

public class MdbUtils {

    public MdbUtils() {
    }



    /**
     * @描述 根据地址获取连接
     * @日期 2023/03/04 17:47
     * @作者 eomer
     **/
    public static Connection getConnection(String url) throws SQLException {
        Connection conn = null;
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            //System.out.println("数据库驱动加载成功");
            conn = DriverManager.getConnection("jdbc:ucanaccess://"+url);
            //System.out.println("数据库连接成功");
            //System.out.print('\n');
        } catch (ClassNotFoundException var3) {
            var3.printStackTrace();
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        }
        return conn;
    }
    /**
     * @描述 关闭连接
     * @日期 2023/03/04 18:33
     * @作者 eomer
     **/
    public static void closeConnection(Connection conn){
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Map<String, List<Map<String, Object>>> readData(String filePath) {
        Map<String, List<Map<String, Object>>> dataMap = new HashMap<>();
        String connectionString = "jdbc:ucanaccess://" + filePath + ";memory=true";

        try (Connection conn = DriverManager.getConnection(connectionString)) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                List<Map<String, Object>> tableData = readTableData(conn, tableName,0);
                dataMap.put(tableName.toLowerCase(), tableData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return dataMap;
    }

    public static List<Map<String, Object>> readTableData(Connection conn, String tableName,Integer nd) throws SQLException {
        List<Map<String, Object>> dataList = new ArrayList<>();
        String query = "SELECT * FROM [" + tableName+ "] where ND ="+ nd;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i).toLowerCase();
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                dataList.add(row);
            }
        }

        return dataList;
    }

    public static Connection openMdbConnection(String filePath) throws SQLException {
        String url = "jdbc:ucanaccess://" + filePath;
        return DriverManager.getConnection(url);
    }
    public static void main(String[] args) {
        String filePath = "C:\\Users\\全权\\Desktop\\司家营2020-2023.mdb";
        readData(filePath);
    }






    // 根据Map类取值
    public static List<Map<String,Object>> populate(ResultSet rs, List<String> fields) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCount = rsmd.getColumnCount();
        List<Map<String,Object>> list = new ArrayList();
        while (rs.next()) {
            Map<String,Object> item = new HashMap<>();
            for (int i = 1; i <= colCount; ++i) {
                Object value = rs.getObject(i);
                for (String field : fields) {
                    if (field.equalsIgnoreCase(toCamelCase(rsmd.getColumnName(i)))) {
                        item.put(field,value);
                        break;
                    }
                }
            }
            list.add(item);
        }
        return list;
    }

    // 根据实体类取值
    public static List populate(ResultSet rs, Class clazz) throws SQLException, InstantiationException, IllegalAccessException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCount = rsmd.getColumnCount();
        List list = new ArrayList();
        Field[] fields = clazz.getDeclaredFields();
        while (rs.next()) {
            Object obj = clazz.newInstance();
            for (int i = 1; i <= colCount; ++i) {
                Object value = rs.getObject(i);
                if (value!=null&&!value.toString().equals("")) {
                    for (int j = 0; j < fields.length; ++j) {
                        Field f = fields[j];
                        if (f.getName().equalsIgnoreCase(toCamelCase(rsmd.getColumnName(i)))) {
                            boolean flag = f.isAccessible();
                            f.setAccessible(true);

                            Type genericType = f.getGenericType();
                            if (genericType.getTypeName().equals("java.sql.Timestamp")) {
//                            f.set(obj, JavaUtils.stringToTimestamp(value.toString()));
                                    f.set(obj, Timestamp.valueOf(value.toString()));
                            } else if (genericType.getTypeName().equals("java.lang.Boolean")) {
                                if (value.equals("false")) {
                                    f.set(obj, false);
                                } else if (value.equals("true")) {
                                    f.set(obj, true);
                                }
                            }
//                        else if (genericType.getTypeName().equals("java.lang.Integer")){
//                            if (value != null){
//                                if (value.toString().contains(".")){
//
//                                }
//                            }
//                        }
                            else {
                                f.set(obj, value);
                            }
                            f.setAccessible(flag);
                        }
                    }
                }
            }
            list.add(obj);
        }
        return list;
    }

    /**
     * @描述 连接更新
     **/
    public static int executeUpdate(Connection conn, String sql) throws SQLException {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            return statement.executeUpdate(sql);
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        }
    }
    /**
     * @描述 地址更新
     **/
    public static int executeUpdate(String url, String sql) throws SQLException {
        Statement statement = null;
        Connection conn = getConnection(url);
        try {
            statement = conn.createStatement();
            return statement.executeUpdate(sql);
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        } finally {
            closeConnection(conn);
        }
    }

    /**
     * @描述 传入连接查询
     * @日期 2023/03/04 17:42
     * @作者 eomer
     **/
    public static ResultSet executeQuery(Connection conn, String sql) throws SQLException {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            ResultSet res = statement.executeQuery(sql);
            return res;
        } catch (SQLException var4) {
            throw var4;
        }
    }

    /**
     * @描述 传入地址查询
     * @日期 2023/03/04 17:42
     * @作者 eomer
     **/
    public static ResultSet executeQuery(String url, String sql) throws SQLException {
        Statement statement = null;
        Connection conn = getConnection(url);
        try {
            statement = conn.createStatement();
            ResultSet res = statement.executeQuery(sql);
            return res;
        } catch (SQLException var5) {
            var5.printStackTrace();
            throw var5;
        } finally {
            closeConnection(conn);
        }
    }

    /**
     * @描述 结果转map
     * @日期 2023/03/23 17:50
     * @作者 eomer
     **/
    public static List<Map<String,Object>> executeQueryMap(String url, String sql, List<String> fields) throws SQLException {
        Connection conn = getConnection(url);
        try {
            ResultSet res = executeQuery(conn, sql);
            return populate(res, fields);
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        } finally {
            closeConnection(conn);
        }
    }

    public static List<Map<String,Object>> executeQueryMap(Connection conn, String sql, List<String> fields) throws SQLException {
        try {
            ResultSet res = executeQuery(conn, sql);
            return populate(res, fields);
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        }
    }

    /**
     * @描述 根据地址查询，转实体
     * @日期 2023/03/04 17:43
     * @作者 eomer
     **/
    public static List executeQuery(String url, String sql, Class clazz) throws SQLException, InstantiationException, IllegalAccessException {
        Connection conn = getConnection(url);
        try {
            ResultSet res = executeQuery(conn, sql);
            return populate(res, clazz);
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        } finally {
            closeConnection(conn);
        }
    }
    /**
     * @描述 根据地址查询，转实体
     * @日期 2023/03/04 17:43
     * @作者 eomer
     **/
    public static int selectCountr(String url, String sql) throws SQLException, InstantiationException, IllegalAccessException {
        Connection conn = getConnection(url);
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int count = rs.getInt(1);
                return count;

            }
        }
        return 0;
    }

    /**
     * @描述 根据连接查询，转实体
     * @日期 2023/03/04 17:43
     * @作者 eomer
     **/
    public static List executeQuery(Connection conn, String sql, Class clazz) throws SQLException, InstantiationException, IllegalAccessException {
        try {
            ResultSet res = executeQuery(conn, sql);
            return populate(res, clazz);
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        }
    }

    /**
     * @描述 根据连接，表名，实体 保存数据
     * @日期 2023/03/04 17:43
     * @作者 eomer
     **/
    public static <T> boolean save(Connection conn, String tableName, T data) throws IllegalAccessException, SQLException {
        Class clazz = data.getClass();
        Field[] fields = clazz.getDeclaredFields();
        String sql = "INSERT INTO %s (%s)  VALUES (%s);";
        String columns = "";
        String values = "";
        for (int i = 0; i < fields.length; ++i) {
            Field field = fields[i];
            field.setAccessible(true);
            Object value = field.get(data);
            if (null != value && !"".equals(value)) {
                Type type = field.getGenericType();
                String var12 = type.toString();
                byte var13 = -1;
                switch (var12.hashCode()) {
                    case -1561781994:
                        if (var12.equals("class java.util.Date")) {
                            var13 = 1;
                        }
                        break;
                    case 673016845:
                        if (var12.equals("class java.lang.String")) {
                            var13 = 0;
                        }
                        break;
                }
                switch (var13) {
                    case 0:
                        values = values + "'" + value + "',";
                        break;
                    case 1:
                        values = values + new Date(((Date) value).getTime()) + ",";
                        break;
                    default:
                        values = values + value + ",";
                }
                columns = columns + toUnderScoreCase(field.getName()) + ",";
            }
        }
        columns = columns.substring(0, columns.length() - 1);
        values = values.substring(0, values.length() - 1);
        sql = String.format(sql, tableName, columns, values);
        System.out.println("sql is :" + sql);
        executeUpdate(conn, sql);
        return true;
    }

    /**
     * @作者 eomer
     * @描述 批量插入
     * @日期 2023/03/04 19:18
     * 参数 url 路径
     * 参数 tableName 表名
     * 参数 fileds 字段
     * 参数 data 内容
     * @返回值 boolean
     **/
    public static boolean batchInsert(String url, String tableName, List<FieldInfo> fileds, List<Map<String, Object>> data) throws SQLException {
        boolean isTrue = false;

        Connection conn = getConnection(url);
        try {
            String sql = "INSERT INTO %s (%s)  VALUES (%s);";
            List<String> columns = new ArrayList<>();
            List<String> values = new ArrayList<>();
            for (int i = 0; i < fileds.size(); i++) {
                columns.add(fileds.get(i).getAttname());
                values.add("?");
            }
            sql = String.format(sql, tableName, StringUtils.join(columns,","), StringUtils.join(values,","));

            PreparedStatement prep = conn.prepareStatement(sql);
            // 将连接的自动提交关闭，数据在传送到数据库的过程中相当耗时
            conn.setAutoCommit(false);

            for (int i = 0; i < data.size(); i++) {
                Map item = data.get(i);

                for (int j = 0; j < fileds.size(); j++) {
                    FieldInfo info = fileds.get(j);
                    String type = info.getColtype();
                    int setNo = j+1;
                    if (type.toLowerCase().startsWith("int")){
                        prep.setInt(setNo, getInteger(item.get(info.getAttname())));
                    }
                    else if (type.toLowerCase().startsWith("double") || type.toLowerCase().startsWith("float")){
                        prep.setDouble(setNo, getDouble(item.get(info.getAttname())));
                    }
//                    else if ("string".equals(type)){
//                        prep.setString(j, item.get(info.getName())+"");
//                    }
                    else {
                        prep.setString(setNo, getString(item.get(info.getAttname())));
                    }
                }
                //System.out.println("插入数据 -> "+i);
                prep.addBatch();
            }
            // 预处理批量执行
            int[] updateCounts = prep.executeBatch();
            prep.clearBatch();
            conn.commit();
            isTrue = true;
        } catch (SQLException var4) {
            var4.printStackTrace();
            //throw var4;
        } finally {
            closeConnection(conn);
        }
        return isTrue;
    }

    public static int getInteger(Object params){
        return params==null?0:Integer.parseInt(params+"");
    }
    public static double getDouble(Object params){
        return params==null?0D:Double.parseDouble(params+"");
    }

    public static String getString(Object params){
        return params==null?"":(params+"");
    }

    /**
     * @描述 转驼峰
     * @日期 2023/03/04 17:45
     * @作者 eomer
     **/
    public static String toCamelCase(String s) {
        if (s == null) {
            return null;
        } else {
            StringBuilder sb = new StringBuilder(s.length());
            boolean upperCase = false;
            for (int i = 0; i < s.length(); ++i) {
                char c = s.charAt(i);
                if (c == '_') {
                    upperCase = true;
                } else if (upperCase) {
                    sb.append(Character.toUpperCase(c));
                    upperCase = false;
                } else {
                    sb.append(c);
                }
            }
            return sb.toString();
        }
    }

    /**
     * @描述 转下划线
     * @日期 2023/03/04 17:46
     * @作者 eomer
     **/
    public static String toUnderScoreCase(String s) {
        if (s == null) {
            return null;
        } else {
            StringBuilder sb = new StringBuilder();
            boolean upperCase = false;
            for (int i = 0; i < s.length(); ++i) {
                char c = s.charAt(i);
                boolean nextUpperCase = true;
                if (i < s.length() - 1) {
                    nextUpperCase = Character.isUpperCase(s.charAt(i + 1));
                }
                if (i > 0 && Character.isUpperCase(c)) {
                    if (!upperCase || !nextUpperCase) {
                        sb.append('_');
                    }
                    upperCase = true;
                } else {
                    upperCase = false;
                }
                sb.append(Character.toLowerCase(c));
            }
            return sb.toString();
        }
    }

    /**
     * @描述 地址更新
     **/
    public static int createTable(String url, String sql) throws SQLException {
        PreparedStatement statement = null;
        Connection conn = getConnection(url);
        try {
            statement = conn.prepareStatement(sql);
            return statement.executeUpdate();
        } catch (SQLException var4) {
            var4.printStackTrace();
            throw var4;
        } finally {
            closeConnection(conn);
        }
    }


//    public static void main(String[] args) {
//
//        String url = "D:\\file\\f3d091d8e2de6e9e5885bfc45cb1a799\\7b0ab431513d94a8be1843fc31886d74\\engineering.db";
//        String tableName = "wbs_design";
//        List<FieldInfo> fileds = new ArrayList<>();
//        FieldInfo info = new FieldInfo();
//        info.setAttname("pkid");
//        info.setColtype("string");
//        fileds.add(info);
//
//        FieldInfo info2 = new FieldInfo();
//        info2.setAttname("designname");
//        info2.setColtype("string");
//        fileds.add(info2);
//        List<Map<String, Object>> data = new ArrayList<>();
//        for (int i = 0; i < 5000; i++) {
//            Map<String, Object> item = new HashMap<>();
//            item.put("pkid", UUID.randomUUID().toString());
//            item.put("designname","AAA"+(Math.random()*1000));
//            data.add(item);
//            System.out.println("生成数据 -> "+(i+1));
//        }
//
//        try {
//            long startInt = System.currentTimeMillis();
//            batchInsert(url, tableName, fileds, data);
//            long endInt = System.currentTimeMillis();
//            System.out.println("耗时："+(endInt-startInt)+" ms");
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }

}